﻿using System;
using System.Collections.Generic;
using System.Text;
using SAServices.Model;
using System.Data;

namespace SAServices.DAL
{
    class StandardDAL
    {
        internal static bool AddStandard(Model.ConnectionConext connCtx, Model.StandardInfo newStandardInfo)
        {
            string sql = String.Format("INSERT INTO Standards(Name, Description) Values({0}, {1});",
                         "'" + newStandardInfo.Name + "'",
                          "'" + newStandardInfo.Description + "'");

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static bool UpdateStandard(Model.ConnectionConext connCtx, Model.StandardInfo updateStandardInfo)
        {
            string sql = String.Format("UPDATE Standards SET Name = {0}, Description = {1} WHERE ID = {2};",
                        "'" + updateStandardInfo.Name + "'",
                        "'" + updateStandardInfo.Description + "'",
                        updateStandardInfo.StandardId);

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static bool DeleteStandard(Model.ConnectionConext connCtx, string standardName)
        {
            string sql = String.Format("DELETE FROM Standards WHERE Name = '{0}'", standardName);

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static Model.StandardInfo GetStandard(Model.ConnectionConext connCtx, string standardName)
        {
            StandardInfo retStandardInfo = new StandardInfo();

            string sql = String.Format("SELECT TOP 1 * FROM Standards WHERE Name = '{0}'", standardName);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            if (dt.Rows.Count > 0)
            {
                retStandardInfo.StandardId = Convert.ToInt32(dt.Rows[0]["ID"]);
                retStandardInfo.Name = Convert.ToString(dt.Rows[0]["Name"]);
                retStandardInfo.Description = Convert.ToString(dt.Rows[0]["Description"]);
            }

            return retStandardInfo;
        }

        internal static List<Model.StandardInfo> GetAllStandards(Model.ConnectionConext connCtx)
        {
            List<StandardInfo> retStandardList = new List<StandardInfo>();

            string sql = String.Format("SELECT * FROM Standards");

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
			{
			    StandardInfo retStandardInfo = new StandardInfo();
                retStandardInfo.StandardId = Convert.ToInt32(dt.Rows[i]["ID"]);
                retStandardInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                retStandardInfo.Description = Convert.ToString(dt.Rows[i]["Description"]);
                retStandardList.Add(retStandardInfo);
			}

            return retStandardList;
        }

        internal static StandardInfo GetStandard(ConnectionConext connCtx, int standardId)
        {
            StandardInfo retStandardInfo = new StandardInfo();

            string sql = String.Format("SELECT * FROM Standards WHERE ID = {0}", standardId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            if (dt.Rows.Count > 0)
            {
                retStandardInfo.StandardId = Convert.ToInt32(dt.Rows[0]["ID"]);
                retStandardInfo.Name = Convert.ToString(dt.Rows[0]["Name"]);
                retStandardInfo.Description = Convert.ToString(dt.Rows[0]["Description"]);
            }

            return retStandardInfo;
        }
    }
}
